﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;

namespace Reports.OptDocIncome
{
    public class DataLoader
    {
        public static void GetData(Reports.OptDocIncome.DataSet.DataDataTable data, string ConnectionString, DateTime dateF,DateTime dateT)
        {
            #region строка запроса, Олег увидит - умрет
            string QueryString =
@"
WITH Data(SupplierName,DocDate,DocNum,SupplierDocNum,SupplierDocDate,Sum,VatSum,VatPercent,AptSum,AptVatSum)
AS
(
SELECT     CA.Contragents.Name, CA.Documents.DocDate, CA.Documents.DocNumber, 
CA.Documents.ContragentDocNumber + '(0%)', CA.Documents.ContragentDocDate,
(SELECT     SUM(CD.InputLines.SupplierPrice * CD.InputLines.DocQntty)
FROM         CD.InputLines INNER JOIN
                      CD.Parties ON CD.InputLines.Id = CD.Parties.InputLineId
WHERE     (CD.Parties.DocId = CA.Documents.Id) and ((CD.InputLines.NDSTax = 0)OR(CD.InputLines.NDSTax is null))) AS [Sum],
(SELECT     SUM(CD.InputLines.NDSValue) 
FROM         CD.InputLines INNER JOIN
                      CD.Parties ON CD.InputLines.Id = CD.Parties.InputLineId
WHERE     (CD.Parties.DocId = CA.Documents.Id)and ((CD.InputLines.NDSTax = 0)OR(CD.InputLines.NDSTax is null)))AS VatSum,
0 as VatPercent,
(SELECT     SUM(CD.Parties.RetailPrice * CD.InputLines.DocQntty)
FROM         CD.InputLines INNER JOIN
                      CD.Parties ON CD.InputLines.Id = CD.Parties.InputLineId
WHERE     (CD.Parties.DocId = CA.Documents.Id)and ((CD.InputLines.NDSTax = 0)OR(CD.InputLines.NDSTax is null))) AS AptSum,
(SELECT sum(CD.InputLines.DocQntty * CD.Parties.RetailPrice * (1-(1/(1+ISNULL(CD.InputLines.NDSTax, 0) / cast(100 as decimal)))))   
FROM CD.InputLines INNER JOIN
CD.Parties ON CD.InputLines.Id = CD.Parties.InputLineId
WHERE (CD.Parties.DocId = CA.Documents.Id) and ((CD.InputLines.NDSTax = 0)OR(CD.InputLines.NDSTax is null))
) as AptVatSum
FROM         CA.Documents INNER JOIN
                      CA.Contragents ON CA.Documents.ContragentId = CA.Contragents.Id
WHERE     (CA.Documents.DocTypeId = 2) 
UNION ALL
SELECT     CA.Contragents.Name, CA.Documents.DocDate, CA.Documents.DocNumber, 
CA.Documents.ContragentDocNumber+'(10%)', CA.Documents.ContragentDocDate,
(SELECT     SUM(CD.InputLines.SupplierPrice * CD.InputLines.DocQntty)
FROM         CD.InputLines INNER JOIN
                      CD.Parties ON CD.InputLines.Id = CD.Parties.InputLineId
WHERE     (CD.Parties.DocId = CA.Documents.Id) and (CD.InputLines.NDSTax = 10)) AS [Sum],
(SELECT     SUM(CD.InputLines.NDSValue) 
FROM         CD.InputLines INNER JOIN
                      CD.Parties ON CD.InputLines.Id = CD.Parties.InputLineId
WHERE     (CD.Parties.DocId = CA.Documents.Id)and (CD.InputLines.NDSTax = 10))AS VatSum,
10 as VatPercent,
(SELECT     SUM(CD.Parties.RetailPrice * CD.InputLines.DocQntty)
FROM         CD.InputLines INNER JOIN
                      CD.Parties ON CD.InputLines.Id = CD.Parties.InputLineId
WHERE     (CD.Parties.DocId = CA.Documents.Id)and (CD.InputLines.NDSTax = 10)) AS AptSum,
(SELECT sum(CD.InputLines.DocQntty * CD.Parties.RetailPrice * (1-(1/(1+ISNULL(CD.InputLines.NDSTax, 0) / cast(100 as decimal)))))   
FROM CD.InputLines INNER JOIN
CD.Parties ON CD.InputLines.Id = CD.Parties.InputLineId
WHERE (CD.Parties.DocId = CA.Documents.Id) and (CD.InputLines.NDSTax = 10)
) as AptVatSum
FROM         CA.Documents INNER JOIN
                      CA.Contragents ON CA.Documents.ContragentId = CA.Contragents.Id
WHERE     (CA.Documents.DocTypeId = 2)
UNION ALL
SELECT     CA.Contragents.Name, CA.Documents.DocDate, CA.Documents.DocNumber, 
CA.Documents.ContragentDocNumber + '(18%)', CA.Documents.ContragentDocDate,
(SELECT     SUM(CD.InputLines.SupplierPrice * CD.InputLines.DocQntty)
FROM         CD.InputLines INNER JOIN
                      CD.Parties ON CD.InputLines.Id = CD.Parties.InputLineId
WHERE     (CD.Parties.DocId = CA.Documents.Id) and (CD.InputLines.NDSTax = 18)) AS [Sum],
(SELECT     SUM(CD.InputLines.NDSValue) 
FROM         CD.InputLines INNER JOIN
                      CD.Parties ON CD.InputLines.Id = CD.Parties.InputLineId
WHERE     (CD.Parties.DocId = CA.Documents.Id)and (CD.InputLines.NDSTax = 18))AS VatSum,
18 as VatPercent,
(SELECT     SUM(CD.Parties.RetailPrice * CD.InputLines.DocQntty)
FROM         CD.InputLines INNER JOIN
                      CD.Parties ON CD.InputLines.Id = CD.Parties.InputLineId
WHERE     (CD.Parties.DocId = CA.Documents.Id)and (CD.InputLines.NDSTax = 18)) AS AptSum,
(SELECT sum(CD.InputLines.DocQntty * CD.Parties.RetailPrice * (1-(1/(1+ISNULL(CD.InputLines.NDSTax, 0) / cast(100 as decimal)))))   
FROM CD.InputLines INNER JOIN
CD.Parties ON CD.InputLines.Id = CD.Parties.InputLineId
WHERE (CD.Parties.DocId = CA.Documents.Id) and (CD.InputLines.NDSTax = 18)
) as AptVatSum
FROM         CA.Documents INNER JOIN
                      CA.Contragents ON CA.Documents.ContragentId = CA.Contragents.Id
WHERE     (CA.Documents.DocTypeId = 2) 
)
/*declare @DateF datetime
declare @DateT datetime
set @DateF = '01/01/2006'
set @dateT = '01/01/2007'*/
select SupplierName,DocDate,DocNum,SupplierDocNum,SupplierDocDate,Sum,VatSum,VatPercent,AptSum,AptVatSum
from Data
where (AptVatSum is not null) AND DocDate BETWEEN @DateF AND @DateT
";
            #endregion
            try
            {
                using (SqlConnection connection = new SqlConnection(ConnectionString))
                {
                    SqlCommand command = new SqlCommand(QueryString, connection);
                    SqlParameter DateF = new SqlParameter("@DateF", SqlDbType.DateTime);
                    DateF.Value = dateF;
                    command.Parameters.Add(DateF);
                    SqlParameter DateT = new SqlParameter("@DateT", SqlDbType.DateTime);
                    DateT.Value = dateT;
                    command.Parameters.Add(DateT);
                    SqlDataAdapter adapter = new SqlDataAdapter(command);
                    adapter.Fill(data);
                }
            }
            catch (SqlException se)
            {
                System.Windows.Forms.MessageBox.Show(se.Message);
            }
            catch (Exception ee)
            {
                System.Windows.Forms.MessageBox.Show(ee.Message);
            }

        }
    }
}
